package jdbc02;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * JDBC查询结果集并封装成对象
 */
public class JDBCDemo03 {
    @Test
    public void testSelectSingle() throws Exception{
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.获取一个连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test02?useSSL=false&characterEncoding=UTF-8",
                "root", "root");

        //3.利用PreparedStatement预编译SQL
        //获取商品表中商品编号为8的商品信息
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM product WHERE pid = ?");

        //4.利用PreparedStatement设置参数
        ps.setInt(1, 8);

        //5.利用PreparedStatement执行SQL
        ResultSet rs = ps.executeQuery();

        //6.对结果集进行封装
        while (rs.next()) {
            int pid = rs.getInt("pid");
            String pname = rs.getString("pname");
            double price = rs.getDouble("price");
            int cid = rs.getInt("cid");
            Product product = new Product(pid, pname, price, cid);
            System.out.println(product);
        }

        //7.释放资源
        conn.close();
        ps.close();
        rs.close();
    }

    @Test
    public void SelectAll()throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.获取一个连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test02?useSSL=false&characterEncoding=UTF-8",
                "root", "root");

        //3.利用PreparedStatement预编译SQL
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM product");

        //4.利用PreparedStatement执行SQL
        ResultSet rs = ps.executeQuery();

        //5.遍历查询结果集
        //由于返回多条数据,而每条数据都对应一个对象,那么我们考虑将所有的对象封装一个集合中,方便日后处理
        ArrayList<Product> products = new ArrayList<>();
        while (rs.next()) {
            Product product = new Product(rs.getInt("pid"),
                    rs.getString("pname"),
                    rs.getDouble("price"),
                    rs.getInt("cid"));
            products.add(product);
        }

        products.stream().forEach(System.out::println);

        //6.释放资源
        conn.close();
        ps.close();
        rs.close();
    }
}
